/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.AvancesPorActividadVO;



import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class MetaFianancieraDao extends DBase{


  Connection conn = null;

    public MetaFianancieraDao() {
        super();
    }


    public void  setMetasPorActividad(AvancesPorActividadVO bean, ResultSet rs) throws SQLException{

           bean.setCodMetaAper(Integer.parseInt(rs.getString("num_codmetaaper_pk")));
           bean.setDescMetaAper(rs.getString("tx_umactper"));
           bean.setCantidad(new Double((rs.getString("num_cantactper"))));
           bean.setTipoActPer(rs.getString("tx_tipoactper"));

    }

     public void  setMetasFinanacieras(AvancesPorActividadVO bean, ResultSet rs) throws SQLException{

           bean.setTrimestre1(new Double(rs.getString("mes1")));
           bean.setTrimestre2(new Double(rs.getString("mes2")));
           bean.setTrimestre3(new Double((rs.getString("mes3"))));

           bean.setTrimestre1c(new Double(rs.getString("mes1c")));
           bean.setTrimestre2c(new Double(rs.getString("mes2c")));
           bean.setTrimestre3c(new Double((rs.getString("mes3c"))));

           bean.setTX_OBSERVACION(rs.getString("tx_observacion"));
           bean.setCodFisiMetam(Integer.parseInt(rs.getString("num_codfinametam_pk")));
           bean.setNUM_CODACTPER_PK(Integer.parseInt(rs.getString("NUM_CODACTPER_PK")));

           bean.setCodEtiqueta(Integer.parseInt(rs.getString("codEtiqueta")));

           

    }

    public void  setEtiquetas(AvancesPorActividadVO bean, ResultSet rs) throws SQLException{

           bean.setCodEtiqueta(Integer.parseInt(rs.getString("NUM_CODETIQUETFINAMETAM_PK")));
           bean.setDescEtiqueta(rs.getString("tx_descetiquet"));           
    }

 
  public List getListMetasFinancieras(String id,String trimestre)  throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<AvancesPorActividadVO> lista = new ArrayList<AvancesPorActividadVO>(0);

        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
             if(trimestre.equals("1"))
                sql = "  select isnull(num_mes1finametam,0) as mes1,isnull(num_mes2finametam,0) as mes2,isnull(num_mes3finametam,0) as mes3,isnull(tx_observacion1,'') as tx_observacion , "+
                      "         isnull(num_codetiquetfinametam1_pk,0)as codEtiqueta,isnull(num_mes1finametamc,0) as mes1c,isnull(num_mes2finametamc,0) as mes2c,isnull(num_mes3finametamc,0) as mes3c, ";
             if(trimestre.equals("2"))
                sql= "   select isnull(num_mes4finametam,0) as mes1,isnull(num_mes5finametam,0) as mes2,isnull(num_mes6finametam,0) as mes3 ,isnull(tx_observacion2,'') as tx_observacion, "+
                        "       isnull(num_codetiquetfinametam2_pk,0) as codEtiqueta,isnull(num_mes4finametamc,0) as mes1c,isnull(num_mes5finametamc,0) as mes2c,isnull(num_mes6finametamc,0) as mes3c, ";
             if(trimestre.equals("3"))
                sql= "   select isnull(num_mes7finametam,0) as mes1,isnull(num_mes8finametam,0) as mes2,isnull(num_mes9finametam,0) as mes3,isnull(tx_observacion3,'') as tx_observacion , "+
                     "          isnull(num_codetiquetfinametam3_pk,0) as codEtiqueta ,isnull(num_mes7finametamc,0) as mes1c,isnull(num_mes8finametamc,0) as mes2c,isnull(num_mes9finametamc,0) as mes3c, ";
             if(trimestre.equals("4"))
                sql= "   select isnull(num_mes10finametam,0) as mes1,isnull(num_mes11finametam,0) as mes2,isnull(num_mes12finametam,0) as mes3,isnull(tx_observacion4,'') as tx_observacion ,"+
                     "          isnull(num_codetiquetfinametam4_pk,0)as codEtiqueta ,isnull(num_mes10finametamc,0) as mes1c,isnull(num_mes11finametamc,0) as mes2c,isnull(num_mes12finametamc,0) as mes3c, ";;

            sql=sql+ " NUM_CODACTPER_PK,num_codfinametam_pk from me_finametam where NUM_CODACTPER_PK="+id;

            rs = stmt.executeQuery(sql);
            AvancesPorActividadVO bean = null;
            while (rs.next()) {
                bean = new AvancesPorActividadVO();
                setMetasFinanacieras(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

 public List getListEtiquetas(String id,String trimestre)  throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<AvancesPorActividadVO> lista = new ArrayList<AvancesPorActividadVO>(0);

        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
             
                sql = "  select * from ME_etiquetfinametam ";


            rs = stmt.executeQuery(sql);
            AvancesPorActividadVO bean = null;
            while (rs.next()) {
                bean = new AvancesPorActividadVO();
                setEtiquetas(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



      public String spMan(AvancesPorActividadVO bean) {
        String msj = "";

        try {

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_update_MeFinaMetam(?,?,?,?,?,?,?,?)}");
            cs.setString(1, bean.getTipoOper());
            cs.setDouble(2, bean.getTrimestre1c());
            cs.setDouble(3, bean.getTrimestre2c());

            cs.setDouble(4, bean.getTrimestre3c());
            cs.setString(5, bean.getTX_OBSERVACION());
            cs.setString(6, bean.getTrimestre());
            cs.setInt(7, bean.getCodFisiMetam());
            cs.setInt(8, bean.getCodEtiqueta());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }




}
